package com.warrantyhotel.android.db;

import java.util.HashMap;
import java.util.HashSet;
import java.util.Set;

import android.app.SearchManager;
import android.content.ContentProvider;
import android.content.ContentResolver;
import android.content.ContentValues;
import android.content.Context;
import android.content.UriMatcher;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteQueryBuilder;
import android.net.Uri;
import android.provider.BaseColumns;

import com.warrantyhotel.android.db.WarrantyHotelDBSchema.CountryTable;
import com.warrantyhotel.android.db.WarrantyHotelDBSchema.CurrencyTable;
import com.warrantyhotel.android.db.WarrantyHotelDBSchema.FindStoresTable;
import com.warrantyhotel.android.db.WarrantyHotelDBSchema.GetFreeTable;
import com.warrantyhotel.android.db.WarrantyHotelDBSchema.Inbox_Account_Upload_Transfers_Table;
import com.warrantyhotel.android.db.WarrantyHotelDBSchema.Inbox_Free_Table;
import com.warrantyhotel.android.db.WarrantyHotelDBSchema.Inbox_Information_Table;
import com.warrantyhotel.android.db.WarrantyHotelDBSchema.Inbox_RSS_Table;
import com.warrantyhotel.android.db.WarrantyHotelDBSchema.Inbox_Transactions_Table;
import com.warrantyhotel.android.db.WarrantyHotelDBSchema.Inbox_WarrantyTransfer_Table;
import com.warrantyhotel.android.db.WarrantyHotelDBSchema.Inbox_Warranty_Transfer_Inner_Table;
import com.warrantyhotel.android.db.WarrantyHotelDBSchema.LanguageTable;
import com.warrantyhotel.android.db.WarrantyHotelDBSchema.News_RSS;
import com.warrantyhotel.android.db.WarrantyHotelDBSchema.RetailStoresTable;
import com.warrantyhotel.android.db.WarrantyHotelDBSchema.StateTable;
import com.warrantyhotel.android.db.WarrantyHotelDBSchema.Union_Inbox;
import com.warrantyhotel.android.db.WarrantyHotelDBSchema.WarrantiesTable;
import com.warrantyhotel.android.db.WarrantyHotelDBSchema.Warranty_Country_State;
import com.warrantyhotel.android.model.Keys;
import com.warrantyhotel.android.util.JSONParser;
import com.warrantyhotel.android.util.SelectionBuilder;

/**
 * Content Provider for Warranty Hotel database
 */
public class WarrantyHotelProvider extends ContentProvider {
	private static final UriMatcher sUriMatcher = buildUriMatcher();

	private WarrantyHotelDatabase mDatabase;
	private Context context;

	private static final HashMap<String, String> mColumnMap = buildColumnMap();

	private static final int COUNTRY = 100;
	private static final int STATE = 101;
	private static final int LANGUAGE = 102;
	private static final int CURRENCY = 103;
	private static final int WARRANTY = 104;
	private static final int RSS_NEWS = 105;
	private static final int FIND_STORES = 106;
	private static final int SEARCH_SUGGEST_STORES = 107;
	private static final int REFRESH_SHORTCUT = 108;
	private static final int WARRANTY_COUNTRY_STATE = 109;
	private static final int SEARCH_SUGGEST_WAR = 110;
	private static final int GET_FREE = 111;
	private static final int INBOX_INFO = 112;
	private static final int INBOX_FREE = 113;
	private static final int INBOX_RSS = 114;
	private static final int INBOX_TRANSACTION = 115;
	private static final int INBOX_UNION = 116;
	private static final int RETAIL_STORES = 117;
	private static final int INBOX_WARRANTY_TRANSFER = 118;
	private static final int INBOX_ACCOUNT_UPLOAD_TRANSFERS = 119;
	private static final int INBOX_WARRANTY_TRANSFER_INNER = 120;

	public static final String KEY_WORD = SearchManager.SUGGEST_COLUMN_TEXT_1;

	@Override
	public boolean onCreate() {
		context = getContext();
		mDatabase = new WarrantyHotelDatabase(context);
		return true;
	}

	private static HashMap<String, String> buildColumnMap() {
		HashMap<String, String> map = new HashMap<String, String>();
		map.put(KEY_WORD, KEY_WORD);
		map.put(BaseColumns._ID, "rowid AS " + BaseColumns._ID);
		map.put(SearchManager.SUGGEST_COLUMN_INTENT_DATA_ID, "rowid AS " + SearchManager.SUGGEST_COLUMN_INTENT_DATA_ID);
		return map;
	}

	private static UriMatcher buildUriMatcher() {
		final UriMatcher matcher = new UriMatcher(UriMatcher.NO_MATCH);
		final String authority = WarrantyHotelDBSchema.CONTENT_AUTHORITY;

		matcher.addURI(authority, CountryTable.TABLE_NAME, COUNTRY);
		matcher.addURI(authority, StateTable.TABLE_NAME, STATE);
		matcher.addURI(authority, LanguageTable.TABLE_NAME, LANGUAGE);
		matcher.addURI(authority, CurrencyTable.TABLE_NAME, CURRENCY);
		matcher.addURI(authority, WarrantiesTable.TABLE_NAME, WARRANTY);
		matcher.addURI(authority, Warranty_Country_State.NAME, WARRANTY_COUNTRY_STATE);
		matcher.addURI(authority, News_RSS.TABLE_NAME, RSS_NEWS);
		matcher.addURI(authority, FindStoresTable.TABLE_NAME, FIND_STORES);
		matcher.addURI(authority, FindStoresTable.TABLE_NAME + "/*", FIND_STORES);
		matcher.addURI(authority, "store/" + SearchManager.SUGGEST_URI_PATH_QUERY, SEARCH_SUGGEST_STORES);
		matcher.addURI(authority, "store/" + SearchManager.SUGGEST_URI_PATH_QUERY + "/*", SEARCH_SUGGEST_STORES);
		matcher.addURI(authority, "war/" + SearchManager.SUGGEST_URI_PATH_QUERY, SEARCH_SUGGEST_WAR);
		matcher.addURI(authority, "war/" + SearchManager.SUGGEST_URI_PATH_QUERY + "/*", SEARCH_SUGGEST_WAR);
		matcher.addURI(authority, GetFreeTable.TABLE_NAME, GET_FREE);
		matcher.addURI(authority, Inbox_Information_Table.TABLE_NAME, INBOX_INFO);
		matcher.addURI(authority, Inbox_Free_Table.TABLE_NAME, INBOX_FREE);
		matcher.addURI(authority, Inbox_RSS_Table.TABLE_NAME, INBOX_RSS);
		matcher.addURI(authority, Inbox_Transactions_Table.TABLE_NAME, INBOX_TRANSACTION);
		matcher.addURI(authority, Inbox_WarrantyTransfer_Table.TABLE_NAME, INBOX_WARRANTY_TRANSFER);
		matcher.addURI(authority, Inbox_Account_Upload_Transfers_Table.TABLE_NAME, INBOX_ACCOUNT_UPLOAD_TRANSFERS);
		matcher.addURI(authority, Inbox_Warranty_Transfer_Inner_Table.TABLE_NAME, INBOX_WARRANTY_TRANSFER_INNER);

		matcher.addURI(authority, Union_Inbox.NAME, INBOX_UNION);
		matcher.addURI(authority, RetailStoresTable.TABLE_NAME, RETAIL_STORES);

		return matcher;
	}

	@Override
	public String getType(final Uri uri) {
		final int match = sUriMatcher.match(uri);
		switch (match) {
		case COUNTRY:
			return CountryTable.CONTENT_TYPE;
		case STATE:
			return StateTable.CONTENT_TYPE;
		case LANGUAGE:
			return LanguageTable.CONTENT_TYPE;
		case CURRENCY:
			return CurrencyTable.CONTENT_TYPE;
		case WARRANTY:
			return WarrantiesTable.CONTENT_TYPE;
		case RSS_NEWS:
			return News_RSS.CONTENT_TYPE;
		case FIND_STORES:
			return FindStoresTable.CONTENT_TYPE;
		case SEARCH_SUGGEST_STORES:
			return SearchManager.SUGGEST_MIME_TYPE;
		case SEARCH_SUGGEST_WAR:
			return SearchManager.SUGGEST_MIME_TYPE;
		case REFRESH_SHORTCUT:
			return SearchManager.SHORTCUT_MIME_TYPE;
		case WARRANTY_COUNTRY_STATE:
			return Warranty_Country_State.CONTENT_TYPE;
		case GET_FREE:
			return GetFreeTable.CONTENT_TYPE;
		case INBOX_FREE:
			return Inbox_Free_Table.CONTENT_TYPE;
		case INBOX_INFO:
			return Inbox_Information_Table.CONTENT_TYPE;
		case INBOX_RSS:
			return Inbox_RSS_Table.CONTENT_TYPE;
		case INBOX_TRANSACTION:
			return Inbox_Transactions_Table.CONTENT_TYPE;
		case INBOX_WARRANTY_TRANSFER:
			return Inbox_WarrantyTransfer_Table.CONTENT_TYPE;
		case INBOX_WARRANTY_TRANSFER_INNER:
			return Inbox_Warranty_Transfer_Inner_Table.CONTENT_TYPE;
		case INBOX_ACCOUNT_UPLOAD_TRANSFERS:
			return Inbox_Account_Upload_Transfers_Table.CONTENT_TYPE;
		case INBOX_UNION:
			return Union_Inbox.CONTENT_TYPE;
		case RETAIL_STORES:
			return RetailStoresTable.CONTENT_TYPE;
		default:
			throw new UnsupportedOperationException("Unknown getType uri: " + uri);
		}
	}

	private String getTable(final Uri uri) {
		switch (sUriMatcher.match(uri)) {
		case COUNTRY:
			return CountryTable.TABLE_NAME;
		case STATE:
			return StateTable.TABLE_NAME;
		case LANGUAGE:
			return LanguageTable.TABLE_NAME;
		case CURRENCY:
			return CurrencyTable.TABLE_NAME;
		case WARRANTY:
			return WarrantiesTable.TABLE_NAME;
		case FIND_STORES:
			return FindStoresTable.TABLE_NAME;
		case SEARCH_SUGGEST_STORES:
			return FindStoresTable.TABLE_NAME;
		case SEARCH_SUGGEST_WAR:
			return WarrantiesTable.TABLE_NAME;
		case RSS_NEWS:
			return News_RSS.TABLE_NAME;
		case WARRANTY_COUNTRY_STATE:
			return Warranty_Country_State.NAME;
		case GET_FREE:
			return GetFreeTable.TABLE_NAME;
		case INBOX_FREE:
			return Inbox_Free_Table.TABLE_NAME;
		case INBOX_INFO:
			return Inbox_Information_Table.TABLE_NAME;
		case INBOX_RSS:
			return Inbox_RSS_Table.TABLE_NAME;
		case INBOX_TRANSACTION:
			return Inbox_Transactions_Table.TABLE_NAME;
		case INBOX_WARRANTY_TRANSFER:
			return Inbox_WarrantyTransfer_Table.TABLE_NAME;
		case INBOX_WARRANTY_TRANSFER_INNER:
			return Inbox_Warranty_Transfer_Inner_Table.TABLE_NAME;
		case INBOX_ACCOUNT_UPLOAD_TRANSFERS:
			return Inbox_Account_Upload_Transfers_Table.TABLE_NAME;
		case INBOX_UNION:
			return Union_Inbox.NAME;
		case RETAIL_STORES:
			return RetailStoresTable.TABLE_NAME;
		default:
			throw new UnsupportedOperationException("Unknown query uri: " + uri);
		}
	}

	@Override
	public Uri insert(final Uri uri, final ContentValues values) {
		final SQLiteDatabase db = mDatabase.getWritableDatabase();
		// final ContentResolver resolver = getContext().getContentResolver();

		db.insertOrThrow(getTable(uri), null, values);
		// resolver.notifyChange(uri, null);

		return uri;
	}

	@Override
	public int delete(final Uri uri, final String selection, final String[] selectionArgs) {
		final SQLiteDatabase db = mDatabase.getWritableDatabase();
		return db.delete(getTable(uri), selection, selectionArgs);
	}

	@Override
	public Cursor query(final Uri uri, String[] projection, String selection, String[] selectionArgs, String sortOrder) {
		final SQLiteDatabase db = mDatabase.getWritableDatabase();

		final SQLiteQueryBuilder builder = new SQLiteQueryBuilder();

		String table;

		int match = sUriMatcher.match(uri);
		switch (match) {
		case COUNTRY:
			table = CountryTable.TABLE_NAME;
			sortOrder = CountryTable.NAME_HACK + " ASC";
			break;
		case STATE:
			table = StateTable.TABLE_NAME;
			sortOrder = StateTable.STATE_NAME_HACK + " ASC";
			break;
		case LANGUAGE:
			table = LanguageTable.TABLE_NAME;
			sortOrder = LanguageTable.LANGUAGE_ID + " ASC";
			break;
		case GET_FREE:
			table = GetFreeTable.TABLE_NAME;
			break;
		case CURRENCY:
			table = CurrencyTable.TABLE_NAME;
			break;
		case WARRANTY:
			table = WarrantiesTable.TABLE_NAME;
			// TEMPORARY hack
			sortOrder = WarrantiesTable.CREATED_DATE + " DESC";
			break;
		case RSS_NEWS:
			table = News_RSS.TABLE_NAME;
			break;
		case INBOX_FREE:
			table = Inbox_Free_Table.TABLE_NAME;
			break;
		case INBOX_INFO:
			table = Inbox_Information_Table.TABLE_NAME;
			break;
		case INBOX_RSS:
			table = Inbox_RSS_Table.TABLE_NAME;
			break;
		case INBOX_TRANSACTION:
			table = Inbox_Transactions_Table.TABLE_NAME;
			break;
		case INBOX_ACCOUNT_UPLOAD_TRANSFERS:
			table = Inbox_Account_Upload_Transfers_Table.TABLE_NAME;
			break;
		case INBOX_WARRANTY_TRANSFER:
			table = Inbox_WarrantyTransfer_Table.TABLE_NAME;
			break;
		case INBOX_WARRANTY_TRANSFER_INNER:
			table = Inbox_Warranty_Transfer_Inner_Table.TABLE_NAME;
			break;
		case RETAIL_STORES:
			table = RetailStoresTable.TABLE_NAME;
			break;
		case INBOX_UNION:
			String[] innerProjectionFree = new String[] { BaseColumns._ID, Inbox_Free_Table.GET_FREE_ID, Inbox_Free_Table.ACTION_TITLE,
					Inbox_Free_Table.ACTION_TEXT, Inbox_Free_Table.NAME_TABLE, Inbox_Free_Table.ISVISIBLE, Inbox_Free_Table.END_DATE };
			String[] innerProjectionInfo = new String[] { BaseColumns._ID, Inbox_Information_Table.INFO_ID, Inbox_Information_Table.TITLE,
					Inbox_Information_Table.TEXT, Inbox_Information_Table.NAME_TABLE, Inbox_Information_Table.ISVISIBLE };
			String[] innerProjectionRSS = new String[] { BaseColumns._ID, Inbox_RSS_Table.GUID, Inbox_RSS_Table.TITLE, Inbox_RSS_Table.CONTENT,
					Inbox_RSS_Table.NAME_TABLE };
			String[] innerTransaction = new String[] { BaseColumns._ID, Inbox_Transactions_Table.GUID, Inbox_Transactions_Table.TITLE,
					Inbox_Transactions_Table.TEASER, Inbox_Transactions_Table.NAME_TABLE };
			String[] innerWarrantyTransfer = new String[] { Inbox_WarrantyTransfer_Table._ID, Inbox_WarrantyTransfer_Table.GUID,
					Inbox_WarrantyTransfer_Table.TITLE, Inbox_WarrantyTransfer_Table.TEASER, Inbox_WarrantyTransfer_Table.NAME_TABLE };
			String[] innerAccountUploadTransfer = new String[] { Inbox_Account_Upload_Transfers_Table._ID, Inbox_Account_Upload_Transfers_Table.GUID,
					Inbox_Account_Upload_Transfers_Table.TITLE, Inbox_Account_Upload_Transfers_Table.TEASER, Inbox_Account_Upload_Transfers_Table.NAME_TABLE };

			SQLiteQueryBuilder getFreeQueryBuilder = new SQLiteQueryBuilder();
			SQLiteQueryBuilder infoQueryBuilder = new SQLiteQueryBuilder();
			SQLiteQueryBuilder rssQueryBuilder = new SQLiteQueryBuilder();
			SQLiteQueryBuilder transactionQueryBuilder = new SQLiteQueryBuilder();
			SQLiteQueryBuilder warrantyTransferQueryBuilder = new SQLiteQueryBuilder();
			SQLiteQueryBuilder warrantyAccountUploadQueryBuilder = new SQLiteQueryBuilder();

			getFreeQueryBuilder.setTables(Inbox_Free_Table.TABLE_NAME);
			infoQueryBuilder.setTables(Inbox_Information_Table.TABLE_NAME);
			rssQueryBuilder.setTables(Inbox_RSS_Table.TABLE_NAME);
			transactionQueryBuilder.setTables(Inbox_Transactions_Table.TABLE_NAME);
			warrantyTransferQueryBuilder.setTables(Inbox_WarrantyTransfer_Table.TABLE_NAME);
			warrantyAccountUploadQueryBuilder.setTables(Inbox_Account_Upload_Transfers_Table.TABLE_NAME);

			Set<String> map = new HashSet<String>();
			map.add(BaseColumns._ID);

			String freeInboxSubQuery = getFreeQueryBuilder.buildUnionSubQuery(null, innerProjectionFree, map, 6, null, Inbox_Free_Table.ISVISIBLE + " = "
					+ JSONParser.INBOX_ITEM_VISIBLE, selectionArgs, null, null);
			String infoInboxSubQuery = infoQueryBuilder.buildUnionSubQuery(null, innerProjectionInfo, map, 6, null, Inbox_Information_Table.ISVISIBLE + " = "
					+ JSONParser.INBOX_ITEM_VISIBLE, selectionArgs, null, null);
			String rssInboxSubQuery = rssQueryBuilder.buildUnionSubQuery(null, innerProjectionRSS, map, 6, null, null, selectionArgs, null, null);
			String transactionInboxSubQuery = transactionQueryBuilder.buildUnionSubQuery(null, innerTransaction, map, 6, null, null, selectionArgs, null, null);
			String warrantyTransferInboxSubQuery = warrantyTransferQueryBuilder.buildUnionSubQuery(null, innerWarrantyTransfer, map, 6, null, null,
					selectionArgs, null, null);
			String accountUploadTransferInboxSubQuery = warrantyAccountUploadQueryBuilder.buildUnionSubQuery(null, innerAccountUploadTransfer, map, 6, null,
					null, selectionArgs, null, null);

			SQLiteQueryBuilder unionQueryBuilder = new SQLiteQueryBuilder();

			// 1. Information
			// 2. Free
			// 3. Transactions
			// 4. WarrantyTransfers
			// 5. AccountUploadTransfers
			// 6. RSSItems

			String unionQuery = unionQueryBuilder.buildUnionQuery(new String[] { infoInboxSubQuery, freeInboxSubQuery, transactionInboxSubQuery,
					warrantyTransferInboxSubQuery, accountUploadTransferInboxSubQuery, rssInboxSubQuery }, null, null);

			SQLiteQueryBuilder outerQueryBuilder = new SQLiteQueryBuilder();

			outerQueryBuilder.setTables("(" + unionQuery + ")");

			String outerQuery = outerQueryBuilder.buildQuery(projection, selection, null, null, null, sortOrder, null);

			return db.rawQuery(outerQuery, new String[0]);

		case FIND_STORES:
			table = FindStoresTable.TABLE_NAME;
			if (uri.getPathSegments().size() > 1 && uri.getPathSegments().get(1).equals(Keys.distinct))
				builder.setDistinct(true);
			break;
		case SEARCH_SUGGEST_STORES:
			projection = new String[] { BaseColumns._ID, FindStoresTable.NAME, SearchManager.SUGGEST_COLUMN_INTENT_DATA_ID };
			builder.setProjectionMap(mColumnMap);
			table = FindStoresTable.TABLE_NAME;
			selection = FindStoresTable.NAME + " like  '%" + selectionArgs[0] + "%'";
			selectionArgs = null;
			break;
		case SEARCH_SUGGEST_WAR:
			projection = new String[] { BaseColumns._ID, WarrantiesTable.LIST_LABEL, SearchManager.SUGGEST_COLUMN_INTENT_DATA_ID };
			builder.setProjectionMap(mColumnMap);
			table = WarrantiesTable.TABLE_NAME;
			selection = WarrantiesTable.LIST_LABEL + " like  '%" + selectionArgs[0] + "%'";
			selectionArgs = null;
			break;
		default:
			final SelectionBuilder builder1 = buildExpandedSelection(uri, match);
			return builder1.where(selection, selectionArgs).query(db, projection, sortOrder);
		}

		builder.setTables(table);

		final Cursor cursor = builder.query(db, projection, selection, selectionArgs, null, null, sortOrder);
		cursor.setNotificationUri(getContext().getContentResolver(), uri);
		return cursor;
	}

	private SelectionBuilder buildExpandedSelection(Uri uri, int match) {
		final SelectionBuilder builder = new SelectionBuilder();
		switch (match) {
		case WARRANTY_COUNTRY_STATE:
			return builder.table(WARRANTY_JOIN_COUNTRY).mapToTable(WarrantiesTable._ID, WarrantiesTable.TABLE_NAME);

		default:
			throw new UnsupportedOperationException("Unknown uri: " + uri);
		}
	}

	String WARRANTY_JOIN_COUNTRY = WarrantiesTable.TABLE_NAME + " LEFT JOIN " + CountryTable.TABLE_NAME + " ON " + WarrantiesTable.TABLE_NAME + "."
			+ WarrantiesTable.ADDRESS_COUNTRY_ID + "=" + CountryTable.TABLE_NAME + "." + CountryTable.COUNTRY_ID + " LEFT JOIN " + StateTable.TABLE_NAME
			+ " ON " + WarrantiesTable.TABLE_NAME + "." + WarrantiesTable.ADDRESS_STATE_ID + "=" + StateTable.TABLE_NAME + "." + StateTable.STATE_ID;

	@Override
	public int update(final Uri uri, final ContentValues values, final String selection, final String[] selectionArgs) {
		int affectdRows;
		final SQLiteDatabase db = mDatabase.getWritableDatabase();
		final ContentResolver resolver = getContext().getContentResolver();

		affectdRows = db.update(getTable(uri), values, selection, selectionArgs);
		resolver.notifyChange(uri, null);

		return affectdRows;

	}

}
